﻿-- =============================================
-- Author:		<赵洪涛>
-- Create date: <2012-05-05>
-- Description:	<根据项目编号获取使用分公司相同项目的接口人>
-- =============================================
CREATE PROCEDURE [dbo].[proc_ProjectInterface_GetList_ByPId] 
(
	@ProjectId uniqueidentifier
)
AS
BEGIN
	Declare @ProductId int,@CompanyId int
	Select @ProductId=IsNull(ProductId,0),@CompanyId=IsNull(CompanyId,0) From Project Where ProjectId=@ProjectId

	;WITH List As(
		Select (Select Mid From [Company] Where [Company].id = p.CompanyId) As Mid,
		IsNull(DepartmentId,0) As DepartmentId,
		IsNull((Select Top 1 d.dname From department d Where d.id = p.departmentid),'') As DepartmentName,
		UserName,
		IsNull((Select Top 1 e.ename From employee e where e.username = p.username),'') As EName,
		CompanyId,
		IsNull((Select CompanyName From [Company] Where [Company].id = p.CompanyId),'') As CompanyName,
		ExCompanyId,
		IsNull((Select CompanyName From [Company] Where [Company].id = p.ExCompanyId),'') As ExCompanyName
		From ProjectInterfaceSet p
		Where p.ProductId=@ProductId And p.CompanyId<>@CompanyId And ExCompanyId<>@CompanyId
		And (ExCompanyId Is Not Null)
		And (UserName Is Not Null) And UserName<>''
	)

	Select DepartmentId,UserName,EName,ExCompanyId,CompanyName As ExCompanyName,
	(Case When CompanyId<>ExCompanyId Then DepartmentName + '(' + ExCompanyName + ')' Else DepartmentName End) As DepartmentName
	From List Where CompanyName<>'' Order By Mid,ExCompanyId
END
